# load modules and functions 
import os
import numpy as np
import pandas as pd
from pandas import DataFrame

import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go

from colorama import Fore, Back, Style
from datetime import time, timedelta

# load DataFrames:
#calls = pd.read_pickle("01_calls.pkl")
#contacts = pd.read_pickle("02_contacts.pkl")
#spend = pd.read_pickle("03_spend.pkl")
deals = pd.read_pickle("04_deals.pkl")

# Group by City and include Latitude & Longitude
city_stats = deals.groupby("City", observed=True).agg(
    Country=("Country", "first"),  # Берём первое значение страны для города
    Total_Deals=("Id", "count"),
    Successful_Deals=("Stage", lambda x: (x == "Payment Done").sum()),    
    Latitude=("Latitude", "first"),  # Берём первое значение координат для города
    Longitude=("Longitude", "first") # Берём первое значение координат для города
).reset_index()

# Calculate success rate
city_stats["Success_Rate (%)"] = (city_stats["Successful_Deals"] / city_stats["Total_Deals"]) * 100

# Filter out 'unknown' cities
city_stats = city_stats[city_stats["City"] != "unknown"]

# Set float display format
pd.set_option('display.float_format', '{:,.2f}'.format)
pd.set_option("display.max_rows", 20) 

# Sort by Total Deals (descending)
city_stats = city_stats.sort_values(by="Successful_Deals", ascending=False)

print(Back.YELLOW + "\nSummary table of Deals Statistic by Cities"  + Style.RESET_ALL)
display(city_stats.head(200))
#city_stats.head(25).plot(kind="bar", x="City",y="Success_Rate (%)")


Summary table of Deals Statistic by Cities

import plotly.express as px
import ipywidgets as widgets
from IPython.display import display

top_cityes = city_stats.sort_values(by="Successful_Deals", ascending=False).head(25)

fig = px.bar(top_cityes, 
             x='City',
             y='Successful_Deals', 
             title=f"Top 25 City by Successful Deals",
             color="Successful_Deals",  # Color depends on values
             color_continuous_scale="RdYlGn",  #  viridis_r
             text_auto=True,  # Добавляем подписи значений
             hover_data=["City", "Country","Successful_Deals"])  # Add Country to the tooltip

fig.update_traces(textfont_size=12, textposition="outside")  # Customize the size and position of signatures
fig.show()

# Creating an interactive map
fig = px.scatter_map(city_stats, 
                     lat="Latitude", 
                     lon="Longitude", 
                     size="Successful_Deals", 
                     color="Success_Rate (%)",
                     hover_name="City",
                      hover_data={
                            "City": False,  # Not needed as it is already displayed in hover_name
                            "Country": True,
                            "Total_Deals": True,
                            "Successful_Deals": True,
                            "Success_Rate (%)": ":.2f", # Limit to 2 decimal places
                            "Latitude": False,  # exclude
                            "Longitude": False  # exclude
                        },
                     color_continuous_scale="RdYlGn", #Plasma
                     title="Map of Total Deals, Successful_Deals and Success_Rate (%)",
                     size_max=30)  # Limiting the maximum point size for visual readability

# Map settings
fig.update_layout(map_style="carto-positron",  
                  map_zoom=5,  
                  map_center={"lat": 51.1657, "lon": 10.4515},
                  legend_title_text="Successful Deals Size")  # Заголовок легенды по language_statssizelanguage_stats

# Adding an explanation for the size of the dots
fig.add_annotation(
    text="Dots size = Successful Deals number",
    xref="paper", yref="paper",
    x=0.01, y=1.05,
    showarrow=False,
    font=dict(size=14, color="blue")
)
fig.show()

country_stats = deals[deals.City != "unknown" ].groupby("Country",observed=False).agg(
    City_num = ("City", "nunique"),
    Total_Deals = ("Id", "count"),
    Successful_Deals = ("Stage", lambda x: (x == "Payment Done").sum() )
).reset_index()

# Calculate success rate
country_stats["Success_Rate (%)"] = (country_stats["Successful_Deals"] / country_stats["Total_Deals"]) * 100

# Set float display format
pd.set_option('display.float_format', '{:,.2f}'.format)
pd.set_option("display.max_rows",20 ) 

# Sort by Total Deals (descending)
country_stats = country_stats.sort_values(by="Total_Deals", ascending=False)

print(Back.YELLOW + "\nSummary table of Deals Statistic by Country" + Style.RESET_ALL)
print(Fore.MAGENTA +f"Total Country: {country_stats.Country.count()}"+ Style.RESET_ALL)
print(Fore.MAGENTA +f"Total City: {country_stats.City_num.sum()}"+ Style.RESET_ALL)
print(Fore.MAGENTA +f"Total Deals: {country_stats.Total_Deals.sum()}"+ Style.RESET_ALL)
print(Fore.MAGENTA +f"Total Successful Deals: {country_stats.Successful_Deals.sum()}"+ Style.RESET_ALL)
print(Fore.MAGENTA +f"Average Successful Rate: {country_stats["Success_Rate (%)"].mean():.2f}%"+ Style.RESET_ALL)

display(country_stats)


fig = px.bar(country_stats, 
                 x='Country',
                 y='Total_Deals', 
                 title=f"Tonal Deals and Successful Deals by Country",
                 log_y=True,
                 color="Successful_Deals",  # Color depends on values
                 color_continuous_scale="RdYlGn",  #  RdYlGn
                 text_auto=True,  # Добавляем подписи значений
                 hover_data={
                 "City_num": True,
                 "Country": True,
                 "Total_Deals": True,
                 "Successful_Deals": True,
                 "Success_Rate (%)": ":.2f"  # Ограничение до 2 знаков после запятой
             })

fig.update_traces(textfont_size=12, textposition="outside")  # Customize the size and position of signatures
fig.show()


Summary table of Deals Statistic by Country
Total Country: 28
Total City: 866
Total Deals: 2159
Total Successful Deals: 722
Average Successful Rate: 55.29%

#deals_filtered = deals[(deals["Level_of_Deutsch"] != "unknown") & (deals["Stage"].isin(["Payment Done", "Lost"]))]
deals_filtered = deals
language_stats = deals_filtered.groupby("Level_of_Deutsch", observed=False).agg(
    Total_Deals=("Id", "count"),
    Successful_Deals = ("Stage", lambda x: (x == "Payment Done").sum()),        
    Lost_Deals = ("Stage", lambda x: (x == "Lost").sum())        
).reset_index()

# коэффициент успешности сделки (%)
language_stats["Conversion_Rate"] = (language_stats["Successful_Deals"] / language_stats["Total_Deals"]) * 100

# Filter out 'unknown' cities
language_stats = language_stats[language_stats["Level_of_Deutsch"] != "unknown"]

# Remove 'unknown' from category list
language_stats["Level_of_Deutsch"] = language_stats["Level_of_Deutsch"].cat.remove_categories("unknown")

# Set float display format
pd.set_option('display.float_format', '{:,.2f}'.format)
pd.set_option("display.max_rows", 20) 

print(Back.YELLOW + "\nSummary table of Deals Statistic by Level of Deutsch" + Style.RESET_ALL)
display(language_stats)

# Create a graph of the dependence of the level of German on the success of the transaction
fig = px.bar(
    language_stats, 
    x="Level_of_Deutsch", 
    y="Successful_Deals", 
    log_y=True,
    color="Conversion_Rate",  # Color depends on values
    color_continuous_scale="viridis_r",  #  viridis_r
    title="Successful Deals and Conversion Rate by German Language Level (log scale)",
    labels={"Conversion_Rate": "Conversion Rate (%)"},
    text_auto=True
)
fig.update_layout(
    yaxis=dict(title="Successful Deals", showgrid=False),
    xaxis=dict(title="Level of Deutsch", tickangle=0)  
)

fig.show()


Summary table of Deals Statistic by Level of Deutsch

conversion_by_city = deals[deals["Level_of_Deutsch"] != "unknown"].groupby(
    ["City", "Level_of_Deutsch"], observed=False
).agg(
    Total_Deals=("Id", "count"),
    Successful_Deals=("Stage", lambda x: (x == "Payment Done").sum())        
).reset_index()

conversion_by_city["Conversion_Rate"] = (conversion_by_city["Successful_Deals"] / conversion_by_city["Total_Deals"]) * 100

top_cities = conversion_by_city[conversion_by_city["Total_Deals"] > 1].groupby(
    "City", observed=False
)["Conversion_Rate"].mean().sort_values(ascending=False).dropna().to_frame().reset_index()

top_cities["City"] = top_cities["City"].astype("str")

# Create dictionaries for adding coordinates
location_map_lat = deals.set_index("City")["Latitude"].to_dict()
location_map_lon = deals.set_index("City")["Longitude"].to_dict()

# Fill in the missing coordinate values
top_cities["Latitude"] = top_cities["City"].map(location_map_lat)
top_cities["Longitude"] = top_cities["City"].map(location_map_lon)

top_cities.loc[top_cities.City == "Kompetenzzentrum Tourismus im Schwarzwald", "City"] = "Schwarzwald"

pd.set_option('display.float_format', '{:,.2f}'.format)
pd.set_option("display.max_rows", 20) 

print(Back.YELLOW + "\nSummary Table of Deals Success Rate depending on Level of German Language by City" + Style.RESET_ALL)
display(top_cities.head(20))


Summary Table of Deals Success Rate depending on Level of German Language by City

# Create a copy of the DataFrame to avoid SettingWithCopyWarning
top_cities = top_cities.copy()

# Convert coordinates to language_statsfloatlanguage_stats
top_cities.loc[:, "Latitude"] = pd.to_numeric(top_cities["Latitude"], errors="coerce")
top_cities.loc[:, "Longitude"] = pd.to_numeric(top_cities["Longitude"], errors="coerce")

# Remove lines with language_stats NaN language_stats if they appeared after the conversion
top_cities = top_cities.dropna(subset=["Latitude", "Longitude"])

# Create an interactive map
fig = px.scatter_map(
    top_cities, 
    lat="Latitude", 
    lon="Longitude", 
    size="Conversion_Rate", 
    color="Conversion_Rate",
    hover_name="City",
    hover_data={
        "City": False,  
        "Conversion_Rate": ":.2f",  
        "Latitude": False,  
        "Longitude": False  
    },
    color_continuous_scale="RdYlGn",
    title="Map of Deal Success Rate by City depending on the level of German",
    size_max=20  
)

fig.update_layout(
    map_style="carto-positron", 
    map_zoom=5,  
    map_center={"lat": 51.1657, "lon": 10.4515},  
    legend_title_text="Successful Deals Size"
)

fig.add_annotation(
    text="Dots size = Conversion Rate",
    xref="paper", yref="paper",
    x=0.01, y=1.05,
    showarrow=False,
    font=dict(size=14, color="blue")
)

fig.show()

import scipy.stats as stats

# Create a copy to avoid SettingWithCopyWarning
deals_filtered = deals[(deals["Level_of_Deutsch"] != "unknown") & (deals["Stage"].isin(["Payment Done", "Lost"]))].copy()

# Encode categories (Payment Done = 1, Lost = 0)
deals_filtered.loc[:, "Stage_Encoded"] = deals_filtered["Stage"].map({"Payment Done": 1, "Lost": 0})

# Create a frequency table (for the chi-square test)
contingency_table = pd.crosstab(deals_filtered["Level_of_Deutsch"], deals_filtered["Stage_Encoded"])

# Run a chi-square test 
chi2, p, dof, expected = stats.chi2_contingency(contingency_table)

# Display the results
print(Fore.MAGENTA + f"Chi2 Statistic: {chi2:.2f}"+ Style.RESET_ALL)
print(Fore.MAGENTA + f"P-value: {p:.5f}"+ Style.RESET_ALL)

Chi2 Statistic: 11.80
P-value: 0.06664

	City	Country	Total_Deals	Successful_Deals	Latitude	Longitude	Success_Rate (%)
86	Berlin	Germany	182	78	52.51	13.40	42.86
516	Munich	Germany	74	27	48.14	11.58	36.49
288	Hamburg	Germany	62	22	53.55	10.00	35.48
434	Leipzig	Germany	45	18	51.34	12.37	40.00
168	Dresden	Germany	28	9	51.05	13.74	32.14
...	...	...	...	...	...	...	...
359	Kamen	Germany	1	1	51.59	7.66	100.00
437	Lengerich	Germany	1	1	52.19	7.85	100.00
438	Lennestadt	Germany	1	1	51.12	8.06	100.00
441	Leopoldshöhe	Germany	1	1	52.01	8.69	100.00
443	Leutershausen	Germany	1	1	49.30	10.41	100.00

	Country	City_num	Total_Deals	Successful_Deals	Success_Rate (%)
6	Germany	769	2033	664	32.66
5	France	23	28	7	25.00
16	Poland	16	22	8	36.36
0	Austria	8	19	11	57.89
17	Russia	9	11	6	54.55
...	...	...	...	...	...
14	Montenegro	1	1	1	100.00
22	Thailand	1	1	1	100.00
21	Tajikistan	1	1	0	0.00
25	United Kingdom	1	1	0	0.00
26	United States	1	1	1	100.00

	Level_of_Deutsch	Total_Deals	Successful_Deals	Lost_Deals	Conversion_Rate
0	A0	85	32	39	37.65
1	A1	26	10	11	38.46
2	A2	137	41	74	29.93
3	B1	780	287	353	36.79
4	B2	162	42	90	25.93
5	C1	25	10	11	40.00
6	C2	3	1	0	33.33

	City	Conversion_Rate	Latitude	Longitude
0	Eppelborn	100.00	49.41	6.96
1	Heidenheim an der Brenz	100.00	48.68	10.15
2	Haldensleben	100.00	52.29	11.41
3	Oldenburg	100.00	53.14	8.21
4	Schwarzwald	100.00	47.98	7.82
5	Oberhausen	83.33	51.47	6.85
6	Wolfsburg	75.00	52.42	10.79
7	Essen	71.43	51.46	7.02
8	Menden (Sauerland)	66.67	51.44	7.80
9	Jena	66.67	50.93	11.59
10	Arnsberg	66.67	51.40	8.06
11	Mülheim an der Ruhr	66.67	51.43	6.88
12	Wuppertal	66.67	51.26	7.18
13	Leipzig	57.78	51.34	12.37
14	Villingen-Schwenningen	55.56	48.06	8.49
15	Munich	53.33	48.14	11.58
16	Dresden	52.22	51.05	13.74
17	Kassel	50.00	51.32	9.50
18	Ludwigsburg	50.00	48.90	9.19
19	Korschenbroich	50.00	51.19	6.51

7 Географический анализ¶

Загрузка модулей и функций¶

7.1 Проанализируйте географическое распределение сделок по городам¶

Cтроим сводную таблицу рейтинга успешных сделок по городам¶

Визуализируем топ 25 городов по числу успешных сделок¶

Отображаем на карте города с числом сделок, количеством успешных из них и коэффициента успешности сделки (коэффициента конверсии)¶

Выводы из анализа географического распределение сделок по городам¶

Фильтрация городов по странам и успешности сделок – определение наиболее перспективных локаций¶

Рассчитаем и визуализируем статистику сделок по странам¶

Выводы из анализа успешных сделок по странам¶

7.2 Изучите влияние уровня знания немецкого языка на успешность сделок в разных городах.¶

Посчитаем и визуализируем статистику успешности сделок в разрезе уровня владения нмецким языком¶

Выводы из анализа успешности сделок в зависимости от уровня знания немецкого языка¶

В каких городах знание языка оказывает наибольшее влияние на успешность сделки?¶

Рассчитаем показатели успешности сделок в зависимости от уровня владения немецким языком по городам¶

Карта успешности сделок по городам в зависимости от уровня немецкого языка¶

Проверим гипотезу о наличии статистической зависимости между уровнем знания немецкого языка и успешностью сделок¶